package com.stxx.framwork.utils;

import cn.hutool.core.bean.BeanUtil;
import cn.hutool.core.util.ObjectUtil;
import cn.hutool.core.util.StrUtil;
import com.alibaba.fastjson2.JSON;
import com.alibaba.fastjson2.JSONArray;
import com.alibaba.fastjson2.JSONObject;
import com.stxx.framwork.annotation.Id;
import com.stxx.framwork.annotation.Transient;
import com.stxx.framwork.constants.LogicType;
import com.stxx.framwork.domain.model.PageQuery;
import com.stxx.framwork.domain.model.PageResult;
import com.stxx.framwork.support.Getter;
import com.stxx.stiot2.domain.*;
import com.stxx.framwork.support.BaseService;
import lombok.extern.slf4j.Slf4j;
import org.noear.solon.annotation.Component;
import org.noear.solon.annotation.Init;
import org.noear.solon.annotation.Inject;

import org.noear.solon.data.sql.SqlBuilder;
import org.noear.solon.data.sql.SqlQuerier;
import org.noear.solon.data.sql.SqlUtils;

import java.lang.invoke.SerializedLambda;
import java.lang.reflect.Field;
import java.lang.reflect.Method;
import java.math.BigDecimal;
import java.math.BigInteger;
import java.sql.SQLException;
import java.time.LocalDateTime;
import java.util.*;
import java.util.concurrent.atomic.AtomicReference;

/**
 * @author Fuzhenlei
 * @date 2025/01/11 16:33
 */
@Slf4j
@Component
public class StoreUtils  {
    private final static Map<Class<?>,List<Field>> fieldCacheMap = new HashMap<>();
    private final static String[] keyword = new String[]{"siganl","code","name"};
    private final static String table_prefix ="iot_";
    //static SqlUtils staticSqlUtils;
    //static List<Consumer<SqlUtils>> consumers = new ArrayList<>();
    //static boolean initialed = false;
    @Inject("config")
    SqlUtils sqlUtils;
    @Init
    public void start() throws SQLException {
        List<String> tables = new ArrayList<>();
        tables.add(getCreatSql(Profile.class));
        tables.add(getCreatSql(Signal.class));
        tables.add(getCreatSql(Device.class));
        tables.add(getCreatSql(Gateway.class));
        tables.add(getCreatSql(Terminal.class));
        tables.add(getCreatSql(SysConfig.class));
        tables.add(getCreatSql(Scene.class));
        tables.add(getCreatSql(Alarm.class));
        tables.add(getCreatSql(EventLog.class));
        tables.forEach(s-> {
            try {
                sqlUtils.sql(s).update();
            } catch (SQLException e) {
                throw new RuntimeException(e);
            }
        });
        //staticSqlUtils = sqlUtils;
//        if(consumers.size()>0){
//            consumers.forEach(consumer->consumer.accept(sqlUtils));
//        }
        //initialed = true;
    }

    String getCreatSql(Class<?> clazz){

        String tableName = getTableName(clazz);
        StringBuilder sb = new StringBuilder();
        sb.append("CREATE TABLE IF NOT EXISTS ").append("`"+tableName+"`").append(" (");
        Field[] fields = clazz.getDeclaredFields();
        String idKey = "";
        List<Field> fieldCache = new ArrayList<>();
        fieldCacheMap.put(clazz,fieldCache);
        for (Field field : fields) {
            if(field.isAnnotationPresent(Transient.class)){
                continue;
            }
            field.setAccessible(true);
            fieldCache.add(field);
            String fieldName = dbColumn(field.getName());
            sb.append(fieldName);

//            if(field.getType()==Integer.class
//                    ||field.getType()==Long.class
//                    ||field.getType()==Boolean.class){
//                sb.append(" INTEGER");
//            }else if(field.getType()== BigDecimal.class
//                    ||field.getType()==Float.class
//                    ||field.getType()==Double.class){
//                sb.append(" REAL");
//            }else{
//                sb.append(" TEXT");
//            }
            if(field.getType()==Integer.class){
                sb.append(" int");
            }else if(field.getType()==Long.class){
                sb.append(" bigint");
            }else if(field.getType()==Boolean.class){
                sb.append(" bit");
            }else if(field.getType()== BigDecimal.class
                    ||field.getType()==Float.class
                    ||field.getType()==Double.class){
                sb.append(" decimal");
            }else{
                sb.append(" varchar(255)");
            }
            Id id = field.getAnnotation(Id.class);
            if(id!=null){
                idKey = fieldName;
                //sb.append(" PRIMARY KEY");
            }
            sb.append(",");
        }
        sb.append("PRIMARY KEY ("+idKey+")");

        //sb.setLength(sb.length()-1);
        sb.append(")");
        System.out.println(sb.toString());
        return sb.toString();
    }

//    public void newAfter(Consumer<SqlUtils> consumer){
//        if(initialed){
//            newAfterRun(consumer);
//        }else{
//            consumers.add(consumer);
//        }
//    }
//    void newAfterRun(Consumer<SqlUtils> consumer){
//
//    }

    public SqlQuerier sql(String sql) {
        return sqlUtils.sql(sql);
    }
    public SqlQuerier sql(String sql,Object...vars) {
        return sqlUtils.sql(sql,vars);
    }

    public SqlQuerier sql(SqlBuilder sql) {
        return sqlUtils.sql(sql);
    }

    public <T> T selectById(Object id,Class<T> tableClass) throws SQLException {
        SqlBuilder sb = new SqlBuilder();
        sb.append("select * from ");
        sb.append(getTableName(tableClass));
        sb.append(" where ");
        sb.append(dbColumn(getIdName(tableClass)));
        sb.append("=?",id);
        return sqlUtils.sql(sb).queryRow(tableClass);
    }

    public <T,P> List<T> selectByIds(Collection<P> ids,Class<T> tableClass) throws SQLException {
        if(ids==null || ids.isEmpty()){
            return null;
        }
        SqlBuilder sb = new SqlBuilder();
        sb.append("select * from ");
        sb.append(getTableName(tableClass));
        sb.append(" where ");
        sb.append(dbColumn(getIdName(tableClass)));
        sb.append(" in (?...)",ids);
        return sqlUtils.sql(sb).queryRowList(tableClass);
    }

    public int insert(Object bean) throws SQLException {
        return replaceOrInsert(bean,false);
    }

    private int replaceOrInsert(Object bean, boolean replace) throws SQLException {
        if(bean==null){
            return 0;
        }
        String table = getTableName(bean.getClass());
        StringBuilder columnStr = new StringBuilder();
        if(replace){
            columnStr.append("replace into ");
        }else {
            columnStr.append("insert into ");
        }
        columnStr.append(table)
                .append(" (");
        List<Field> fields =getFields(bean.getClass());
        Object[] params = new Object[fields.size()];
        Field idField = null;
        Object Idvalue = null;
        for(int i=0;i<fields.size();i++) {
            try {
                Field field = fields.get(i);

                Object value = field.get(bean);
                if(field.isAnnotationPresent(Id.class)){
                    idField = field;
                    Idvalue = value;
                }
                columnStr.append(dbColumn(field.getName())).append(",");
                params[i] = getValue(field,value);
            } catch (IllegalAccessException e) {
                throw new RuntimeException(e);
            }
        }

        columnStr.setLength(columnStr.length()-1);
        columnStr.append(") values (");
        String[] questionMarks = new String[params.length]; // 创建一个String数组，每个元素都是"?"
        Arrays.fill(questionMarks, "?");
        columnStr.append(String.join(",", questionMarks));
        columnStr.append(");");
        int r = 0;
        if(Idvalue==null){
            BigInteger id = sqlUtils.sql(columnStr.toString(),params).<BigInteger>updateReturnKey();
            try{
                if(Integer.class.isAssignableFrom(idField.getType())){
                    idField.set(bean,id.intValue());
                }else {
                    idField.set(bean,id.longValue());
                }
            } catch (IllegalAccessException e) {
                throw new RuntimeException(e);
            }
            r = 1;
        }else{
            r = sqlUtils.sql(columnStr.toString(),params).update();
        }
        return r;

    }

    public int replace(Object bean) throws SQLException {
        return replaceOrInsert(bean,true);
    }

//    public <T> int delete(Class<T> tableClass,Getter<T> getter,Object id) throws SQLException {
//        String table = getTableName(tableClass);
//        StringBuilder sb = new StringBuilder();
//        sb.append("delete from ").append(table).append(" where ").append(dbColumn(columnName(getter))).append(" = ?;");
//        return sqlUtils.sql(sb.toString(),id).update();
//    }

    public <T> int delete(Class<T> tableClass,Object id) throws SQLException {
        String table = getTableName(tableClass);
        StringBuilder sb = new StringBuilder();
        sb.append("delete from ").append(table).append(" where ").append(dbColumn(getIdName(tableClass))).append(" = ?;");
        return sqlUtils.sql(sb.toString(),id).update();
    }

    public <T> int deleteByIds(Class<T> tableClass,Object[] ids) throws SQLException {
        String table = getTableName(tableClass);
        SqlBuilder sqlSpec = new SqlBuilder()
                .append(String.format("delete from %s where %s in (",table,dbColumn(getIdName(tableClass))));
        for (int i = 0; i < ids.length; i++) {
            sqlSpec.append("?,");
        }
        sqlSpec.removeLast();
        sqlSpec.append(")",ids);
        return sqlUtils.sql(sqlSpec).update();
    }

//    public <T> int delete(T bean,Getter<T> ... getters) throws SQLException {
//        String table = getTableName(bean.getClass());
//        StringBuilder sb = new StringBuilder();
//        sb.append("delete from ").append(table).append(" where ");
//        Object[] params = new Object[getters.length];
//
////        JSONObject jsonObject = JSONUtil.parseObj(bean);
//        JSONObject jsonObject = JSONObject.from(bean);
//        for(int i=0;i<getters.length;i++){
//            String columnName = columnName(getters[i]);
//            sb.append(dbColumn(columnName)).append(" = ? and ");
//            params[i]=jsonObject.get(columnName);
//        }
//        sb.setLength(sb.length()-5);
//        return sqlUtils.sql(sb.toString(),params).update();
//    }
    public <T> int update(T bean) throws SQLException {
            return update(bean,true);
    }

    public <T> int update(T bean,Boolean ignoreNull) throws SQLException {
        String table = getTableName(bean.getClass());
        //String idField = getIdName(bean.getClass());
        //JSONObject jsonObject = JSONUtil.parseObj(bean);
        SqlBuilder sb = new SqlBuilder();
        sb.append("update ").append(table).append(" set ");
        List<Field> fields =getFields(bean.getClass());
        Field idField = null;
        Object idValue = null;
        for(Field field : fields) {
            Object value = null;
            try {
                value = field.get(bean);
            } catch (IllegalAccessException e) {
                throw new RuntimeException(e);
            }
            if(ignoreNull&&value==null)continue;
            if(field.isAnnotationPresent(Id.class)){
                idField = field;
                idValue = value;
                continue;
            }
            sb.append( dbColumn(field.getName())).append(" = ?,",getValue(field,value));
        }

        sb.removeLast();
        sb.append(" where ");
        sb.append(dbColumn(idField.getName()));
        sb.append("=?",getValue(idField,idValue));
        return sqlUtils.sql(sb.getSql(),sb.getArgs()).update();
    }

    Object getValue(Field field,Object value){
        if(String.class.isAssignableFrom(field.getType())
                ||Long.class.isAssignableFrom(field.getType())
                ||Integer.class.isAssignableFrom(field.getType())
                ||Boolean.class.isAssignableFrom(field.getType())
                ||BigDecimal.class.isAssignableFrom(field.getType())
                ||LocalDateTime.class.isAssignableFrom(field.getType())
                ||field.getType().isPrimitive()){
            return value;
        }if(field.getType().isEnum()){
            return value.toString();
        }else{
            return JSON.toJSONString(value);
        }
    }



//    public <T> int update(T bean, Getter<T>...fields) throws SQLException {
//        String table = getTableName(bean.getClass());
//        JSONObject jsonObject = JSONObject.from(bean);
//        SqlBuilder sb = new SqlBuilder();
//        sb.append("update ").append(table).append(" set ");
//        jsonObject.forEach((key,value) -> {
//            if(value!=null&&value instanceof JSON){
//                sb.append( StrUtil.toUnderlineCase(key)).append(" = ?,",JSON.toJSONString(value));
//            }else{
//                sb.append( StrUtil.toUnderlineCase(key)).append(" = ?,",value);
//            }
//
//        });
//        sb.removeLast();
//        sb.append(" where ");
//        for (int j = 0; j < fields.length; j++) {
//            String fileName = columnName(fields[j]);
//            sb.append(StrUtil.toUnderlineCase(fileName)).append(" = ? and ",jsonObject.get(fileName));
//        }
//        sb.remove(sb.length()-5,5);
//        sb.append(";");
//        return sqlUtils.sql(sb).update();
//    }

    public <T> List<T> selectList(Class<T> clazz) throws SQLException {
        return selectList(clazz,null);
    }

    public <T> List<T> selectList(Class<T> clazz,T obj) throws SQLException {
        SqlBuilder sb = getListSqlBuilder(clazz,obj);
        sb.insert("select * ");
        return sqlUtils.sql(sb).queryRowList(clazz);
    }


    public <T> PageResult<T> selectPageList(Class<T> clazz, T obj, PageQuery pageQuery) throws SQLException {
        if(pageQuery.getPageNum()==null){
            pageQuery.setPageNum(PageQuery.DEFAULT_PAGE_NUM);
        }
        if(pageQuery.getPageSize()==null){
            pageQuery.setPageSize(PageQuery.DEFAULT_PAGE_SIZE);
        }
        SqlBuilder sb = getListSqlBuilder(clazz,obj);


        sb.backup();
        sb.insert("select * ");
        if(StrUtil.isNotEmpty(pageQuery.getOrderByColumn())){
            if(StrUtil.isBlank(pageQuery.getIsAsc())){
                pageQuery.setIsAsc("asc");
            }
            sb.append(" order by ")
                    .append(pageQuery.getOrderByColumn())
                    .append(" ")
                    .append(pageQuery.getIsAsc());
        }
        sb.append(" limit ?,? ", (pageQuery.getPageNum()-1)*pageQuery.getPageSize(),pageQuery.getPageSize()); //分页获取列表

        List<T> list = sqlUtils.sql(sb).queryRowList(clazz);
        //回滚（可以复用备份前的代码构建）
        sb.restore();
        sb.insert("select count(*)  ");
        //查询总数
        Long total = Long.parseLong(sqlUtils.sql(sb).queryValue().toString());
        PageResult<T> pageResult = new PageResult(list,total);
        return pageResult;
    }

    <T> SqlBuilder getListSqlBuilder(Class<T> clazz, T obj){
        String tableName = getTableName(clazz);
        SqlBuilder sb = new SqlBuilder();
        sb.append(" from ").append(tableName);
        if(obj!=null) {
            JSONObject jsonObject = JSONObject.from(obj);
            AtomicReference<Boolean> isWhere = new AtomicReference<>(false);
            jsonObject.forEach((key, value) -> {
                if (ObjectUtil.isNotEmpty(value)) {
                    String fieldName = dbColumn(key);
                    if (isWhere.get()) {
                        sb.append(" and ");
                    } else {
                        sb.append(" where ");
                        isWhere.set(true);
                    }
                    sb.append(fieldName);
                    LogicType lt = BaseService.getLogic(tableName, fieldName);
                    switch (lt) {
                        case LIKE:
                            sb.append(" like CONCAT('%',?,'%')", value);
                            break;
                        case GT:
                            sb.append(" > ?", value);
                            break;
                        case LT:
                            sb.append(" < ?", value);
                            break;
                        case GE:
                            sb.append(" >= ?", value);
                            break;
                        case LE:
                            sb.append(" <= ?", value);
                            break;
                        case LIKE_LEFT:
                            sb.append(" like CONCAT(?,'%')", value);
                            break;
                        case LIKE_RIGHT:
                            sb.append(" like CONCAT('%',?)", value);
                            break;
                        default:
                            sb.append(" = ?", value);
                            break;
                    }
                }
            });
        }
        return sb;
    }



    public static String getTableName(Class<?> clazz){
        return table_prefix + StrUtil.toUnderlineCase(clazz.getSimpleName());
    }

    public static String getTableName(String tableName){
        return table_prefix + tableName;
    }

    private static String columnName(Getter<?> getter){
        try {
            Method method = getter.getClass().getDeclaredMethod("writeReplace");
            method.setAccessible(Boolean.TRUE);
            SerializedLambda lambda = (SerializedLambda) method.invoke(getter);
            return BeanUtil.getFieldName(lambda.getImplMethodName());
        } catch (Exception e) {
            throw new RuntimeException(e);
        }
    }

    private static String dbColumn(String fieldName){
        return "`"+StrUtil.toUnderlineCase(fieldName)+"`";
    }

    private static String getIdName(Class<?> clazz){
        Field[] fields = clazz.getDeclaredFields();
        for (Field field : fields) {
            if (field.isAnnotationPresent(Id.class)) {
                return field.getName();
            }
        }
        return null;
    }

    public static List<Field> getFields(Class<?> clazz){
        if(fieldCacheMap.containsKey(clazz)){
            return fieldCacheMap.get(clazz);
        }
        List<Field> fields = new ArrayList<>();
        Field[] declaredFields = clazz.getDeclaredFields();
        for (Field field : declaredFields) {
            if (field.isAnnotationPresent(Transient.class)) {
                continue;
            }
            field.setAccessible(true);
            fields.add(field);
        }
        fieldCacheMap.put(clazz, fields);
        return fields;
    }
}
